﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.Entities.Relations.DirectAssociations
{
    /// <summary>
    /// 
    /// </summary>
    public class DbEntityRelationDiscoveryWorker:IWorker 
    {
        #region Implementation of IWorker

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        private Thread workerThread;
        private DbEntityRelationDiscoveryInstruction instruction;
        private DbEntityRelationDiscoveryOutput output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return workerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(DbEntityRelationDiscoveryComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DbEntityRelationDiscoveryInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DbEntityRelationDiscoveryInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = Thread.CurrentThread;
            this.DiscoveryERRelations();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = new Thread(new ThreadStart(this.DiscoveryERRelations));
            this.workerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if (this.workerThread != null && this.workerThread.IsAlive)
            {
                this.workerThread.Abort();
                this.workerThread.Join();

                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
                }
            }
        }

        #endregion

        #region work
        private void DiscoveryERRelations()
        {
            try
            {
                if(this.WorkerStatusChangedEvent !=null )
                {
                    string msg = "Retrieve tables from databases...";
                    int pct = 0;
                    this.WorkerStatusChangedEvent(
                        this._TicketID, this.GetType(), this.instruction, this._WorkflowName, this._ComponentName, msg,
                        pct);
                }
                Dictionary<string, List<DbEntity>> allTables = this.GetAllTableEntities(
                    this.instruction.DbProvider, this.instruction.ConnStr, 
                    this.instruction.Databases);
                int totalTableCount = 0;
                foreach(string dbName in allTables.Keys)
                {
                    totalTableCount += allTables[dbName].Count;
                }
                if (totalTableCount <= 1)
                    throw new Exception("Unable to retrieve relations");

                List<TableReference> tableRefs = new List<TableReference>();
                foreach(string dbName in this.instruction.Databases)
                {
                    List<DbEntity> tablesForDB = allTables[dbName];
                    List<TableReference> tblRefs1 = this.GetTableReferencesForDB(totalTableCount, tablesForDB);
                    tableRefs.AddRange(tblRefs1);

                    if(this.instruction.InferTableRelations)
                    {
                        List<TableReference> tblRefs2 = this.GetTableReferencesForDBUsingReferenceIntegrity(
                            dbName);
                        if(tblRefs2 !=null && tblRefs2.Count>0)
                        {
                            foreach(TableReference tblRef in tblRefs2)
                            {
                                bool exist = false;
                                foreach(TableReference existingRef in tableRefs)
                                {
                                    if(existingRef.Equals(tblRef) || existingRef.Equals(tblRef.ReverseReferenceDirection()))
                                    {
                                        exist = true;
                                        break;
                                    }
                                }
                                if(!exist)
                                    tableRefs.Add(tblRef);
                            }
                        }

                        List<TableReference> tblRefs3 = this.GetTableReferencesUsingSynonyms(dbName, tablesForDB);
                        if (tblRefs3 != null && tblRefs3.Count > 0)
                        {
                            foreach (TableReference tblRef in tblRefs3)
                            {
                                bool exist = false;
                                foreach (TableReference existingRef in tableRefs)
                                {
                                    if (existingRef.Equals(tblRef) || existingRef.Equals(tblRef.ReverseReferenceDirection()))
                                    {
                                        exist = true;
                                        break;
                                    }
                                }
                                if (!exist)
                                    tableRefs.Add(tblRef);
                            }
                        }
                    }
                }

                XmlDocument xDoc = new XmlDocument();
                xDoc.LoadXml("<TableRelations></TableRelations>");
                XmlNode root = xDoc.DocumentElement;
                if(tableRefs !=null && tableRefs.Count>0)
                {
                    foreach(TableReference tblRef in tableRefs)
                    {
                        XmlNode refNode = XmlDataUtil.AddElement(ref xDoc, root, "TableRelation");
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ParentDb", tblRef.ParentDatabase);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ParentSchema", tblRef.ParentSchema);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ParentTable", tblRef.ParentTableName);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "PK", tblRef.ParentColumnName);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ChildDb", tblRef.ChildDatabase);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ChildSchema", tblRef.ChildSchema);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "ChildTable", tblRef.ChildTableName);
                        XmlDataUtil.UpdateAttribute(ref xDoc, refNode, "FK", tblRef.ChildColumnName);
                    }
                }
                xDoc.Save(this.instruction.ResultFilePath);

                this.output=new DbEntityRelationDiscoveryOutput(this.instruction.OwnerComponentID, this._TicketID,
                    tableRefs, this.instruction.ResultFilePath);
                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
            }
            catch(Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
            }
        }
        #endregion

        #region 
        /// <summary>
        /// infer by pk-fk within one db 
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="totalTableCount"></param>
        /// <returns></returns>
        private List<TableReference> GetTableReferencesForDB(
            int totalTableCount,
            List<DbEntity> tables)
        {
            List<TableReference> tableRefs = new List<TableReference>();
            int tableProcessed = 0;
            for (int i = 0; i < tables.Count; i++)
            {
                if (this.WorkerStatusChangedEvent != null)
                {
                    int pct = tableProcessed * 100 / totalTableCount;
                    string msg = string.Format(
                        "Determining relations for table {0}, {1} of {2} processed, {3} relations found.",
                        tables[i].DbName + "." + tables[i].SchemaName + "." + tables[i].EntityName,
                        tableProcessed, totalTableCount, tableRefs.Count);
                    this.WorkerStatusChangedEvent(
                        this._TicketID, this.GetType(), this.instruction, this._WorkflowName, this._ComponentName, msg,
                        pct);
                }
                for (int k = i + 1; k < tables.Count; k++)
                {
                    TableReference tableRef = new TableReference(string.Empty, string.Empty, string.Empty, string.Empty);
                    try
                    {
                        bool isRef = this.HasReference(tables[i], tables[k], ref tableRef);
                        if (isRef)
                            tableRefs.Add(tableRef);
                    }
                    catch (Exception ex1)
                    {
                        if (this.WorkerErredEvent != null)
                            this.WorkerErredEvent(
                                this._TicketID, this.GetType(), this.instruction, ex1.ToString(), false);
                    }

                }

                tableProcessed++;
            }

            return tableRefs;
        }

        /// <summary>
        /// infer by col name and data type and reference integrtity
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private List<TableReference> GetTableReferencesForDBUsingReferenceIntegrity(string dbName)
        {
            int totalInferred = 0;
            List<TableReference> tableRefs = new List<TableReference>();
            string connStr = this.instruction.ConnStr;
            Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
            Match dbMatch = dbRegex.Match(connStr);
            if (dbMatch.Success)
            {
                string dbName0 = dbMatch.Groups[1].Value;
                connStr = this.instruction.ConnStr.Replace(dbName0, dbName);
            }

            List<TableReference> possibleTableRefs =
                this.GetPossibleTableRelationsInDatabase(connStr);
            if(possibleTableRefs==null || possibleTableRefs.Count==0)
                return tableRefs;

            foreach (TableReference possibleTableRef in possibleTableRefs)
            {
                if (this.WorkerStatusChangedEvent != null)
                {
                    int pct = totalInferred * 100 / possibleTableRefs.Count;
                    string msg = string.Format(
                        "Inferring relations for table {0}, {1} of {2} processed, {3} relations found.",
                        possibleTableRef.ParentDatabase + "." + possibleTableRef.ParentSchema + "." +
                        possibleTableRef.ParentColumnName, totalInferred,
                        possibleTableRefs.Count, tableRefs.Count);
                    this.WorkerStatusChangedEvent(
                        this._TicketID, this.GetType(), this.instruction, this._WorkflowName, this._ComponentName, msg,
                        pct);
                }

                TableReference tblRef = new TableReference(
                    string.Empty, string.Empty, string.Empty, string.Empty);
                try
                {
                    DbEntity tbl1 = new DbEntity(
                        possibleTableRef.ParentDatabase, possibleTableRef.ParentSchema,
                        possibleTableRef.ParentTableName);
                    DbEntity tbl2 = new DbEntity(
                        possibleTableRef.ChildDatabase, possibleTableRef.ChildSchema,
                        possibleTableRef.ChildTableName);
                    bool hasRef = false;
                    tblRef = this.InferTableReference(
                        ref hasRef, this.instruction.ConnStr,
                        tbl1.DbName, tbl1.SchemaName, tbl1.EntityName, possibleTableRef.ParentColumnName,
                        tbl2.DbName, tbl2.SchemaName, tbl2.EntityName, possibleTableRef.ChildColumnName);
                    if (hasRef)
                    {
                        tableRefs.Add(tblRef);
                        totalInferred++;
                    }
                }
                catch (Exception ex1)
                {
                    if (this.WorkerErredEvent != null)
                    {
                        this.WorkerErredEvent(
                            this._TicketID, this.GetType(), this.instruction, ex1.ToString(), false);
                    }
                }

                totalInferred++;
            }

            return tableRefs;
        }

        /// <summary>
        /// synonyms
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private List<TableReference> GetTableReferencesUsingSynonyms(
            string dbName, List<DbEntity> tables)
        {
            List<TableReference> tableRefs = new List<TableReference>();
            int tableInferred = 0;
            string connStr = this.instruction.ConnStr;
            Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
            Match dbMatch = dbRegex.Match(connStr);
            if (dbMatch.Success)
            {
                string dbName0 = dbMatch.Groups[1].Value;
                connStr = this.instruction.ConnStr.Replace(dbName0, dbName);
            }
            List<TableSynonym> synonyms = DbUtil.GetSynonyms(connStr);

            foreach (TableSynonym synonym in synonyms)
            {
                if(synonym.DbName.ToLower()==dbName.ToLower())
                    continue;

                string dbHost = DbConnUtil.GetHost(this.instruction.ConnStr);
                string instance = DbConnUtil.GetDbServerInstance(this.instruction.ConnStr);
                string uid = DbConnUtil.GetUserID(this.instruction.ConnStr);
                string pwd = DbConnUtil.GetPassword(this.instruction.ConnStr);
                string dataSrc = dbHost + (string.IsNullOrEmpty(instance) ? "" : "/" + instance);
                string synonymConnStr = string.Format(
                    "Server={0};database={1};integrated security=sspi;", dataSrc, synonym.DbName);
                if (!string.IsNullOrEmpty(uid) && !string.IsNullOrEmpty(pwd))
                    synonymConnStr = string.Format(
                        "Server={0};database={1};User ID={2};Password={3}", 
                        dataSrc, synonym.DbName, uid, pwd);
                if (!DbUtil.CanQueryTable(DataProviderType.MSSQL, synonymConnStr, synonym.SchemaName, synonym.TableName))
                    continue;

                DbEntity parentTbl = new DbEntity(synonym.DbName, synonym.SchemaName, synonym.TableName);
                if (this.WorkerStatusChangedEvent != null)
                {
                    int pct = tableInferred * 100 / synonyms.Count;
                    string msg = string.Format(
                        "Inferring relations for table {0}, {1} of {2} processed, {3} relations found.",
                        parentTbl.DbName +"."+parentTbl.SchemaName+"."+ parentTbl.EntityName, 
                        tableInferred, synonyms.Count, tableRefs.Count);
                    this.WorkerStatusChangedEvent(
                        this._TicketID, this.GetType(), this.instruction, this._WorkflowName, this._ComponentName, msg,
                        pct);
                }

                foreach (DbEntity childTbl in tables)
                {
                    List<TableReference> possibleTblRefs = this.GetPossibleTableRelations(
                        this.instruction.ConnStr, parentTbl.DbName, parentTbl.SchemaName, parentTbl.EntityName,
                        childTbl.DbName, childTbl.SchemaName, childTbl.EntityName);
                    if(possibleTblRefs !=null && possibleTblRefs.Count>0)
                    {
                        foreach(TableReference possibleTblRef in possibleTblRefs)
                        {
                            bool hasRef = false;
                            TableReference tblRef = this.InferTableReference(
                                ref hasRef, this.instruction.ConnStr,
                                possibleTblRef.ParentDatabase, possibleTblRef.ParentSchema,
                                possibleTblRef.ParentTableName, possibleTblRef.ParentColumnName,
                                possibleTblRef.ChildDatabase, possibleTblRef.ChildSchema, possibleTblRef.ChildTableName,
                                possibleTblRef.ChildColumnName);
                            if(hasRef)
                                tableRefs.Add(tblRef);
                        }
                    }
                }

                tableInferred++;
            }

            return tableRefs;
        }
        #endregion

        #region all entities
        private Dictionary<string,List<DbEntity>> GetAllTableEntities(
            DataProviderType dbProvider, string connStr, List<string> dbNames)
        {
            Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
            Dictionary<string, List<DbEntity>> tablesByDB = new Dictionary<string, List<DbEntity>>();
            foreach(string dbName in dbNames)
            {
                List<DbEntity> tables=new List<DbEntity>();
                Match dbMatch = dbRegex.Match(connStr);
                string connStr2 = connStr;
                if(dbMatch.Success) 
                {
                    connStr2 = connStr.Replace(dbMatch.Groups[1].Value, dbName);
                }
                string[] schemaNames = DbUtil.GetSchemaNames(dbProvider, connStr2);
                if(schemaNames !=null && schemaNames.Length>0)
                {
                    foreach(string schemaName in schemaNames)
                    {
                        if (DbUtil.CanQuery(dbProvider, connStr2, schemaName))
                        {
                            string[] tblNames = DbUtil.GetTableNames(dbProvider, schemaName, connStr2);
                            if (tblNames != null && tblNames.Length > 0)
                            {
                                foreach (string tblName in tblNames)
                                {
                                    if (DbUtil.CanQueryTable(dbProvider, connStr2, schemaName, tblName))
                                    {
                                        DbEntity table = new DbEntity(dbName, schemaName, tblName);
                                        tables.Add(table);
                                    }
                                }
                            }
                        }
                    }
                }
                if(tables.Count>0)
                {
                    tablesByDB.Add(dbName, tables);
                }
            }
            return tablesByDB;
        }

        private List<TableReference> GetPossibleTableRelationsInDatabase(string connStr)
        {
            List<TableReference> allTableRefs=new List<TableReference>();
            string dbName = DbConnUtil.GetDatabaseName(connStr);
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = @"select 
	c.[name] as SchemaName,  
	b.[name] as TableName,
	a.[name] as ColumnName,
	a.System_Type_ID as ColDataType
from 
	sys.columns a 
	left join sys.objects b on a.[object_id]=b.[object_id] 
	left join sys.Schemas c on b.[schema_id]=c.[schema_id]
where 
	replace(a.[name],'_','') in (
		select 
			replace([name],'_','') 
		from sys.columns 
		where 
			[object_id] in (select [object_id] from sys.objects where type='u')
			and is_nullable=0 and scale=0
		group by replace([name],'_','')
		having count (replace([name],'_','')) >1 ) 
	and c.[name] <>'sys'
order by replace(a.[name],'_','')";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                string prevColName = string.Empty;
                int prevColDataTypeID = 0;
                Dictionary<DbEntity,string> relatedEntityColumns = new Dictionary<DbEntity, string>();
                while(reader.Read())
                {
                    string colName = reader["ColumnName"].ToString();
                    int colDataTypeID = int.Parse(reader["ColDataType"].ToString());
                    string schemaName = reader["SchemaName"].ToString();
                    string tableName = reader["TableName"].ToString();
                    DbEntity entity = new DbEntity(dbName, schemaName, tableName);
                    if(this.ColumnMatches(colName,prevColName) && colDataTypeID==prevColDataTypeID)
                    {
                        relatedEntityColumns.Add(entity, colName);
                    }
                    else
                    {
                        if (relatedEntityColumns.Count > 1)
                        {
                            Dictionary<DbEntity, string> relatedEntityColumns2 = new Dictionary<DbEntity, string>();
                            foreach (DbEntity relatedEntity in relatedEntityColumns.Keys)
                            {
                                if(DbUtil.IsTableColumnIndexed(connStr,relatedEntity.EntityName,
                                    relatedEntityColumns[relatedEntity]))
                                    relatedEntityColumns2.Add(relatedEntity, relatedEntityColumns[relatedEntity]);
                            }
                            if (relatedEntityColumns2.Count > 1)
                            {
                                relatedEntityColumns = relatedEntityColumns2;
                                List<DbEntity> relatedEntities=new List<DbEntity>();
                                relatedEntities.AddRange(relatedEntityColumns.Keys);
                                for (int i = 0; i < relatedEntities.Count; i++)
                                {
                                    DbEntity entity1 = relatedEntities[i];
                                    string colName1 = relatedEntityColumns[entity1];
                                    for (int k = i + 1; k < relatedEntities.Count; k++)
                                    {
                                        DbEntity entity2 = relatedEntities[k];
                                        string colName2 = relatedEntityColumns[entity2];
                                        TableReference tblRef = new TableReference(
                                            entity1.DbName, entity1.SchemaName, entity1.EntityName, colName1,
                                            entity2.DbName, entity2.SchemaName, entity2.EntityName, colName2);
                                        allTableRefs.Add(tblRef);
                                    }
                                }
                            }
                        }

                        relatedEntityColumns=new Dictionary<DbEntity, string>();
                        relatedEntityColumns.Add(entity, colName);
                        prevColName = colName;
                        prevColDataTypeID = colDataTypeID;
                    }
                }
                reader.Close();

                if (relatedEntityColumns.Count > 1)
                {
                    List<DbEntity> relatedEntities =  new List<DbEntity>();
                    relatedEntities.AddRange(relatedEntityColumns.Keys);
                    for (int i = 0; i < relatedEntities.Count; i++)
                    {
                        DbEntity entity1 = relatedEntities[i];
                        string colName1 = relatedEntityColumns[entity1];
                        for (int k = i + 1; k < relatedEntities.Count; k++)
                        {
                            DbEntity entity2 = relatedEntities[k];
                            string colName2 = relatedEntityColumns[entity2];
                            TableReference tblRef = new TableReference(
                                entity1.DbName, entity1.SchemaName, entity1.EntityName, colName1,
                                entity2.DbName, entity2.SchemaName, entity2.EntityName, colName2);
                            allTableRefs.Add(tblRef);
                        }
                    }
                    relatedEntities = new List<DbEntity>();
                }

                // 

            }
            catch (Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), false);
            }
            finally
            {
                conn.Close();
            }

            return allTableRefs;
        }

        private bool ColumnMatches(string colName1, string colName2)
        {
            colName1 = StringUtil.RemoveNonAlphaNumericCharacters(colName1);
            colName2 = StringUtil.RemoveNonAlphaNumericCharacters(colName2);
            if (colName1.ToLower() == colName2.ToLower())
                return true;
            else
                return false;
        }

        private bool SqlDbTypeMatches(SqlDbType dbType1, SqlDbType dbType2)
        {
            if(dbType2==SqlDbType.BigInt || dbType2==SqlDbType.SmallInt || dbType2==SqlDbType.TinyInt)
                dbType2 = SqlDbType.Int;
            if (dbType1 == SqlDbType.BigInt || dbType1 == SqlDbType.SmallInt || dbType1 == SqlDbType.TinyInt)
                dbType1 = SqlDbType.Int;
            if (dbType2 == SqlDbType.Real || dbType2 == SqlDbType.Money || dbType2==SqlDbType.Float || dbType2==SqlDbType.Decimal)
                dbType2 = SqlDbType.Decimal;
            if (dbType1 == SqlDbType.Real || dbType1 == SqlDbType.Money || dbType1 == SqlDbType.Float || dbType1 == SqlDbType.Decimal)
                dbType1 = SqlDbType.Decimal;

            if(dbType1==dbType2)
                return true;
            else
                return false;
        }

        private List<TableReference> GetPossibleTableRelations(string connStr, 
            string dbName1, string schema1, string tblName1,
            string dbName2, string schema2, string tblName2)
        {
            string connStr1 = connStr;
            string connStr2 = connStr;
            Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
            Match dbMatch = dbRegex.Match(connStr);
            if (dbMatch.Success)
            {
                string dbName0 = dbMatch.Groups[1].Value;
                connStr1 = connStr.Replace(dbName0, dbName1);
                connStr2 = connStr.Replace(dbName0, dbName2);
            }
            TableColumn[] cols1 = DbUtil.GetTableColumns(
                DataProviderType.MSSQL,
                connStr1, schema1, tblName1);
            TableColumn[] cols2 = DbUtil.GetTableColumns(
                DataProviderType.MSSQL,
                connStr2, schema2, tblName2);
            List<TableReference> tblRefs=new List<TableReference>();

            if(cols1==null || cols2==null)
                return tblRefs;

            foreach (TableColumn col1 in cols1)
            {
                foreach(TableColumn col2 in cols2)
                {
                    if(this.ColumnMatches(col1.ColumnName, col2.ColumnName) && 
                        col1.DbType==col2.DbType)
                    {
                        TableReference tblRef = new TableReference(
                            dbName1, schema1, tblName1, col1.ColumnName,
                            dbName2, schema2, tblName2, col2.ColumnName);
                        tblRefs.Add(tblRef);
                        break;
                    }
                }
            }
            return tblRefs;
        }
        #endregion

        #region relations
        /// <summary>
        /// colname/data type matches
        /// indexed
        /// non-null value for pk
        /// pks contains all fks
        /// </summary>
        /// <param name="isTblRef"></param>
        /// <param name="connStr"></param>
        /// <param name="dbName1"></param>
        /// <param name="schemaName1"></param>
        /// <param name="tblName1"></param>
        /// <param name="colName1"></param>
        /// <param name="dbName2"></param>
        /// <param name="schemaName2"></param>
        /// <param name="tblName2"></param>
        /// <param name="colName2"></param>
        /// <returns></returns>
        private TableReference InferTableReference(ref bool isTblRef, string connStr,
            string dbName1, string schemaName1, string tblName1, string colName1,
            string dbName2, string schemaName2, string tblName2, string colName2)
        {
            isTblRef = false;
            TableReference tblRef=new TableReference(string.Empty,string.Empty,string.Empty, string.Empty,
                string.Empty, string.Empty, string.Empty, string.Empty);
            string connStr1 = connStr;
            string connStr2 = connStr;
            Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
            Match dbMatch = dbRegex.Match(connStr);
            if (dbMatch.Success)
            {
                string dbName0 = dbMatch.Groups[1].Value;
                connStr1 = connStr.Replace(dbName0, dbName1);
                connStr2 = connStr.Replace(dbName0, dbName2);
            }

            // indexed?
            bool isIndexed1 = DbUtil.IsTableColumnIndexed(connStr1,tblName1, colName1);
            if(!isIndexed1)
                return tblRef;
            bool isIndexed2 = DbUtil.IsTableColumnIndexed(connStr2, tblName2, colName2);
            if (!isIndexed2)
                return tblRef;
            // db type?
            TableColumn col1 = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr1, schemaName1, tblName1, colName1);
            TableColumn col2 = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr2, schemaName2, tblName2, colName2);
            if(col1.DbType != col2.DbType)
                return tblRef;
            // contains null value?
            bool containsNullValue1 = DbUtil.ContainsNullValue(
                DataProviderType.MSSQL, connStr1, schemaName1, tblName1, colName1);
            bool containsNullValue2 = DbUtil.ContainsNullValue(
                DataProviderType.MSSQL, connStr2, schemaName2, tblName2, colName2);
            if(containsNullValue1 && containsNullValue2)
                return tblRef;
            // pk contain all fk
            if(!containsNullValue1 && DbUtil.ContainsAllValues(DataProviderType.MSSQL,connStr1,
                dbName1, schemaName1,tblName1,colName1,
                dbName2, schemaName2,tblName2,colName2))
            {
                tblRef = new TableReference(dbName1,schemaName1,tblName1,colName1,
                    dbName2,schemaName2,tblName2,colName2);
                isTblRef = true;
            }
            else if(!containsNullValue2 && DbUtil.ContainsAllValues(DataProviderType.MSSQL, connStr2,
                dbName2, schemaName2,tblName2,colName2,
                dbName1, schemaName1,tblName1,colName1))
            {
                tblRef = new TableReference(dbName2, schemaName2, tblName2, colName2,
                    dbName1, schemaName1, tblName1, colName1);
                isTblRef = true;
            }
            
            return tblRef;
        }

        private bool HasReference(DbEntity tbl1, DbEntity tbl2, ref TableReference tblRef)
        {
            bool hasRelation = false;
            if(tbl1.DbName==tbl2.DbName)
            {
                Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
                string connStr = this.instruction.ConnStr;
                Match dbMatch = dbRegex.Match(connStr);
                if(dbMatch.Success)
                {
                    string dbName0 = dbMatch.Groups[1].Value;
                    connStr = connStr.Replace(dbName0, tbl1.DbName);
                }
                tblRef = DbUtil.GetTableReference(
                    connStr, tbl1.SchemaName, tbl1.EntityName, tbl2.SchemaName, tbl2.EntityName);
                if(tblRef.ParentTableName==tbl1.EntityName && tblRef.ChildTableName==tbl2.EntityName)
                {
                    hasRelation = true;
                    tblRef.ParentDatabase = tblRef.ChildDatabase = tbl1.DbName;
                    tblRef.ParentSchema = tbl1.SchemaName;
                    tblRef.ChildSchema = tbl2.SchemaName;
                }
                else if (tblRef.ParentTableName == tbl2.EntityName && tblRef.ChildTableName == tbl1.EntityName)
                {
                    hasRelation = true;
                    tblRef.ParentDatabase = tblRef.ChildDatabase = tbl1.DbName;
                    tblRef.ParentSchema = tbl2.SchemaName;
                    tblRef.ChildSchema = tbl1.SchemaName;
                }
            }
            return hasRelation;
        }

        //private bool HasCompatibleColumns(DbEntity parentTbl, DbEntity childTbl, ref TableReference tblRef)
        //{
        //    string connStr1 = this.instruction.ConnStr;
        //    string connStr2 = this.instruction.ConnStr;
        //    Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
        //    Match dbMatch = dbRegex.Match(connStr1);
        //    if(dbMatch.Success)
        //    {
        //        string dbName0 = dbMatch.Groups[1].Value;
        //        connStr1 = this.instruction.ConnStr.Replace(dbName0, parentTbl.DbName);
        //        connStr2 = this.instruction.ConnStr.Replace(dbName0, childTbl.DbName);
        //    }

        //    List<string> pkFieldNames = DbUtil.GetTablePrimaryKeys(connStr1, "", parentTbl.EntityName);
        //    if(pkFieldNames !=null && pkFieldNames.Count>0)
        //    {
        //        if(DbUtil.CanQueryTable(DataProviderType.MSSQL, connStr1,parentTbl.SchemaName,parentTbl.EntityName))
        //        {
        //            Dictionary<string, SqlDbType> pkDbTypes = new Dictionary<string, SqlDbType>();
        //            foreach (string pkFieldName in pkFieldNames)
        //            {
        //                TableColumn pkCol = DbUtil.GetTableColumn(
        //                    DataProviderType.MSSQL, connStr1, parentTbl.SchemaName, parentTbl.EntityName, pkFieldName);
        //                if(string.IsNullOrEmpty(pkCol.ColumnName))
        //                    continue;
        //                pkDbTypes.Add(pkCol.ColumnName.ToLower(), pkCol.DbType);
        //            }
        //            TableColumn[] fkCols = DbUtil.GetTableColumns(
        //                DataProviderType.MSSQL, connStr2, childTbl.SchemaName, childTbl.EntityName);
        //            if (fkCols == null || fkCols.Length == 0)
        //                return false;

        //            foreach (TableColumn fkCol in fkCols)
        //            {
        //                if (pkDbTypes.ContainsKey(fkCol.ColumnName.ToLower()) &&
        //                    pkDbTypes[fkCol.ColumnName.ToLower()] == fkCol.DbType)
        //                {
        //                    List<object> fieldValues = DbUtil.SampleTableFieldData(
        //                        DataProviderType.MSSQL, connStr2, childTbl.SchemaName, childTbl.EntityName, fkCol.ColumnName,
        //                        10, true);
        //                    if (fieldValues != null && fieldValues.Count > 0)
        //                    {
        //                        try
        //                        {
        //                            bool pkTblContainsFKValues = DbUtil.TableFieldContainsAll(
        //                            DataProviderType.MSSQL, connStr1, parentTbl.SchemaName, parentTbl.EntityName,
        //                            fkCol.ColumnName, fieldValues);
        //                            if (pkTblContainsFKValues)
        //                            {
        //                                tblRef = new TableReference(parentTbl.DbName, parentTbl.SchemaName, parentTbl.EntityName, fkCol.ColumnName,
        //                                    childTbl.DbName, childTbl.SchemaName, childTbl.EntityName, fkCol.ColumnName);
        //                                return true;
        //                            }
        //                        }
        //                        catch (Exception)
        //                        {
        //                            return false;
        //                        }
        //                    }
        //                }
        //            }
        //        }
        //    }
        //    return false;
        //}

        //private bool HasCompatibleColumns(DbEntity parentTbl, DbEntity childTbl, string colName, ref TableReference tblRef)
        //{
        //    string connStr1 = this.instruction.ConnStr;
        //    string connStr2 = this.instruction.ConnStr;
        //    Regex dbRegex = new Regex(@"Database\s*=\s*([^=;]+)", RegexOptions.IgnoreCase);
        //    Match dbMatch = dbRegex.Match(connStr1);
        //    if (dbMatch.Success)
        //    {
        //        string dbName0 = dbMatch.Groups[1].Value;
        //        connStr1 = this.instruction.ConnStr.Replace(dbName0, parentTbl.DbName);
        //        connStr2 = this.instruction.ConnStr.Replace(dbName0, childTbl.DbName);
        //    }

        //    List<object> fieldValues2 = DbUtil.SampleTableFieldData(
        //        DataProviderType.MSSQL, connStr2, childTbl.SchemaName, 
        //        childTbl.EntityName, colName, 10, false);
        //    if (fieldValues2 != null && fieldValues2.Count > 0)
        //    {
        //        try
        //        {
        //            bool pkTblContainsFKValues = DbUtil.TableFieldContainsAll(
        //                DataProviderType.MSSQL, connStr1, parentTbl.SchemaName, parentTbl.EntityName,
        //                colName, fieldValues2);
        //            if (pkTblContainsFKValues)
        //            {
        //                tblRef = new TableReference(
        //                    parentTbl.DbName, parentTbl.SchemaName, parentTbl.EntityName, colName,
        //                    childTbl.DbName, childTbl.SchemaName, childTbl.EntityName, colName);
        //                return true;
        //            }
        //        }
        //        catch (Exception)
        //        {
        //        }
        //    }
        //    List<object> fieldValues1 = DbUtil.SampleTableFieldData(
        //        DataProviderType.MSSQL, connStr1, parentTbl.SchemaName,
        //        parentTbl.EntityName, colName, 10, false);
        //    if (fieldValues1 != null && fieldValues1.Count > 0)
        //    {
        //        try
        //        {
        //            bool pkTblContainsFKValues = DbUtil.TableFieldContainsAll(
        //                DataProviderType.MSSQL, connStr2, childTbl.SchemaName,
        //                childTbl.EntityName, colName, fieldValues1);
        //            if (pkTblContainsFKValues)
        //            {
        //                tblRef = new TableReference(
        //                    childTbl.DbName, childTbl.SchemaName, childTbl.EntityName, colName,
        //                    parentTbl.DbName, parentTbl.SchemaName, parentTbl.EntityName, colName);
        //                return true;
        //            }
        //        }
        //        catch (Exception)
        //        {
        //        }
        //    }

        //    return false;
        //}
        #endregion

        #region sampling

        #endregion
    }
}
